package com.jarveis.frame.jdbc;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;

import com.jarveis.frame.bean.BeanProperty;
import com.jarveis.frame.bean.BeanUtil;
import com.jarveis.frame.cache.CacheManager;
import com.jarveis.frame.jdbc.ant.TableInfo;
import com.jarveis.frame.jdbc.handler.BeanHandler;
import com.jarveis.frame.lang.StringUtil;

/**
 * 数据库连接工具类
 * 
 * @author liuguojun
 */
public final class JdbcUtil {

	private static Logger logger = Logger.getLogger(JdbcUtil.class);

	/**
	 * 批量操作数据库
	 * 
	 * @param sql
	 * @param params
	 * @return int[]
	 * @throws SQLException
	 */
	public static int[] batch(String sql, Object[][] params)
			throws SQLException {
		return batch(JdbcManager.getConnection(), sql, params);
	}

	/**
	 * 批量操作数据库
	 * 
	 * @param conn
	 * @param sql
	 * @param params
	 * @return int[]
	 * @throws SQLException
	 */
	public static int[] batch(Connection conn, String sql, Object[][] params)
			throws SQLException {
		return new JdbcQuery(conn).batch(getSql(sql), params);
	}

	/**
	 * 批量操作数据库
	 * 
	 * @param sql
	 * @param params
	 * @return int[]
	 * @throws SQLException
	 */
	public static int[] batch(String sql, List params) throws SQLException {
		return batch(JdbcManager.getConnection(), sql, params);
	}

	/**
	 * 批量操作数据库
	 * 
	 * @param conn
	 * @param sql
	 * @param params
	 * @return int[]
	 * @throws SQLException
	 */
	public static int[] batch(Connection conn, String sql, List params)
			throws SQLException {
		return new JdbcQuery(conn).batch(getSql(sql), params);
	}

	/**
	 * 更新(插入、修改、删除)数据库
	 * 
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	public static int excute(String sql, Object[] params) throws SQLException {
		return excute(JdbcManager.getConnection(), sql, params);
	}

	/**
	 * 更新(插入、修改、删除)数据库
	 * 
	 * @param conn
	 * @param sql
	 * @param params
	 * @return int
	 * @throws SQLException
	 */
	public static int excute(Connection conn, String sql, Object[] params)
			throws SQLException {
		return new JdbcQuery(conn).update(getSql(sql), params);
	}

	/**
	 * 查询数据库
	 * 
	 * @param sql
	 * @param rsh
	 * @return
	 * @throws SQLException
	 */
	public static Object query(String sql, ResultSetHandler rsh)
			throws SQLException {
		return query(JdbcManager.getConnection(), getSql(sql), rsh);
	}

	/**
	 * 查询数据库
	 * 
	 * @param conn
	 * @param sql
	 * @param rsh
	 * @return Object
	 * @throws SQLException
	 */
	public static Object query(Connection conn, String sql, ResultSetHandler rsh)
			throws SQLException {
		return new JdbcQuery(conn).query(getSql(sql), rsh, null);
	}

	/**
	 * 查询数据库
	 * 
	 * @param conn
	 * @param sql
	 * @param rsh
	 * @param params
	 * @return Object
	 * @throws SQLException
	 */
	public static Object query(Connection conn, String sql,
			ResultSetHandler rsh, Object[] params) throws SQLException {
		return new JdbcQuery(conn).query(getSql(sql), rsh, params);
	}

	/**
	 * 通过命名参数的SQL语句来查询数据库
	 * 
	 * @param sql
	 * @param rsh
	 * @param params
	 * @return Object
	 * @throws SQLException
	 */
	public static Object query(String sql, ResultSetHandler rsh, Map params)
			throws SQLException {
		Connection conn = JdbcManager.getConnection();
		Object result = query(conn, sql, rsh, params);
		return result;
	}

	/**
	 * 通过命名参数的SQL语句来查询数据库
	 * 
	 * @param conn
	 * @param sql
	 * @param rsh
	 * @param params
	 * @return Object
	 * @throws SQLException
	 */
	public static Object query(Connection conn, String sql,
			ResultSetHandler rsh, Map params) throws SQLException {
		Object[] results = SqlParserFactory.query(getSql(sql), params);
		return query(conn, (String) results[0], rsh, (Object[]) results[1]);
	}

	/**
	 * 通过对象来查询数据库
	 * 
	 * @param s
	 * @param rsh
	 * @return Object
	 * @throws SQLException
	 */
	public static Object query(Serializable s, ResultSetHandler rsh)
			throws SQLException {
		Connection conn = JdbcManager.getConnection();
		Object result = query(conn, s, rsh);
		return result;
	}

	/**
	 * 通过对象来查询数据库
	 * 
	 * @param conn
	 * @param s
	 * @param rsh
	 * @return Object
	 * @throws SQLException
	 */
	public static Object query(Connection conn, Serializable s,
			ResultSetHandler rsh) throws SQLException {
		Object[] results = SqlParserFactory.query(s);
		return query(conn, (String) results[0], rsh, (Object[]) results[1]);
	}

	/**
	 * 加载对象
	 * 
	 * @param type
	 * @param id
	 * @return Object
	 * @throws SQLException
	 */
	public static Object load(Class type, Serializable id) throws SQLException {
		Connection conn = JdbcManager.getConnection();
		Object result = load(conn, type, id);
		return result;
	}

	/**
	 * 加载对象
	 * 
	 * @param conn
	 * @param type
	 * @param id
	 * @return Object
	 * @throws SQLException
	 */
	public static Object load(Connection conn, Class type, Serializable id)
			throws SQLException {
		Object[] results = SqlParserFactory.loadById(type, id);
		return query(conn, (String) results[0], new BeanHandler(type),
				(Object[]) results[1]);
	}

	/**
	 * 加载对象
	 * 
	 * @param conn
	 *            数据库连接对象
	 * @param sql
	 * @param type
	 * @param id
	 * @return Object
	 * @throws SQLException
	 */
	public static Object load(Connection conn, String sql, Class type,
			Serializable s) throws SQLException {
		Map params = BeanProperty.toMap(s);
		Object[] results = SqlParserFactory.query(getSql(sql), params);
		return query(conn, (String) results[0], new BeanHandler(type),
				(Object[]) results[1]);
	}

	/**
	 * 保存对象
	 * 
	 * @param s
	 *            实体对象
	 * @return int
	 * @throws SQLException
	 */
	public static int save(Serializable s) throws SQLException {
		Connection conn = JdbcManager.getConnection();
		int result = save(conn, s);
		return result;
	}

	/**
	 * 保存对象
	 * 
	 * @param conn
	 *            数据库连接对象
	 * @param s
	 *            实体对象
	 * @return int
	 * @throws SQLException
	 */
	public static int save(Connection conn, Serializable s) throws SQLException {
		Object[] results = SqlParserFactory.insert(s);
		return excute(conn, (String) results[0], (Object[]) results[1]);
	}

	/**
	 * 更新对象
	 * 
	 * @param s
	 *            实体对象
	 * @param id
	 *            主键
	 * @return
	 * @throws SQLException
	 */
	public static int update(Serializable s, Serializable id)
			throws SQLException {
		Connection conn = JdbcManager.getConnection();
		int result = update(conn, s, id);
		return result;
	}

	/**
	 * 更新对象
	 * 
	 * @param conn
	 *            数据库连接对象
	 * @param s
	 *            实体对象
	 * @param id
	 *            主键
	 * @return int
	 * @throws SQLException
	 */
	public static int update(Connection conn, Serializable s, Serializable id)
			throws SQLException {
		Object[] results = SqlParserFactory.updateById(s, id);
		return excute(conn, (String) results[0], (Object[]) results[1]);
	}

	/**
	 * 删除对象
	 * 
	 * @param type
	 *            实体类型
	 * @param id
	 *            主键
	 * @return int
	 * @throws SQLException
	 */
	public static int delete(Class type, Serializable id) throws SQLException {
		Connection conn = JdbcManager.getConnection();
		int result = delete(conn, type, id);
		return result;
	}

	/**
	 * 删除对象
	 * 
	 * @param conn
	 *            数据库连接对象
	 * @param type
	 *            实体类型
	 * @param id
	 *            主键
	 * @return int
	 * @throws SQLException
	 */
	public static int delete(Connection conn, Class type, Serializable id)
			throws SQLException {
		Object[] results = SqlParserFactory.deleteById(type, id);
		return excute(conn, (String) results[0], (Object[]) results[1]);
	}

	/**
	 * 获取当前事务模式
	 * 
	 * @return boolean
	 * @throws SQLException
	 */
	public static boolean getAutoCommit() throws SQLException {
		Connection conn = JdbcManager.getConnection();
		return conn.getAutoCommit();
	}

	/**
	 * 当前数据操作开启事务
	 * 
	 * @throws SQLException
	 */
	public static void startTransaction() throws SQLException {
		Connection conn = JdbcManager.getConnection();
		conn.setAutoCommit(false);
	}

	/**
	 * 提交事务
	 * 
	 * @throws SQLException
	 */
	public static void commit() throws SQLException {
		Connection conn = JdbcManager.getConnection();
		if (conn != null && !conn.isClosed()) {
			conn.commit();
		}
	}

	/**
	 * 事务回滚
	 * 
	 * @throws SQLException
	 */
	public static void rollback() throws SQLException {
		Connection conn = JdbcManager.getConnection();
		if (conn != null && !conn.isClosed()) {
			conn.rollback();
		}
	}

	/**
	 * 获取Sql缓存
	 * 
	 * @param sql
	 * @return String
	 */
	private static String getSql(String sql) {
		if (sql.trim().length() > 0) {
			String str = (String) CacheManager.getInstance().getCacheValue(
					JdbcManager.CACHE_JDBC_SQL, sql);
			if (!StringUtil.isEmpty(str)) {
				sql = str;
			}
		}
		return sql;
	}

	/**
	 * 获取实体类的表名称
	 * 
	 * @param s
	 * @return
	 */
	public static String getClassTable(Serializable s) {
		String name = BeanUtil.getFname(s);
		TableInfo tableInfo = (TableInfo) CacheManager.getInstance()
				.getCacheValue(JdbcManager.CACHE_CORE_TABLE, name);

		if (tableInfo == null) {
			tableInfo = new TableInfo(s);
			CacheManager.getInstance().putCacheValue(
					JdbcManager.CACHE_CORE_TABLE, name, tableInfo);
		}

		return tableInfo.getTable();
	}
}
